/*DONE* Table [dbo].[<CompanyName>$LSC Posted CO Header$5ecfc871-5d82-43f1-9c54-59685e82318d]  1/1  Script Date: 14.1.2021 - JI ******/
/*Add prestaging tables for all companies in LSInsight$Companies table*/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DECLARE @CreateTable as VARCHAR(MAX)
DECLARE @Company as VARCHAR(100)
DECLARE @CompanyCounter as INT
DECLARE @i as INT

SET @i = 0
SET @CompanyCounter = (SELECT
		MAX([Company])
	FROM LSInsight$Companies)

WHILE @i <= @CompanyCounter 
BEGIN
SET @Company = (SELECT
		LSInsight$Companies.[CompanyPrefix]
	FROM dbo.LSInsight$Companies
	WHERE [Company] = @i)

IF @Company IS NOT NULL
BEGIN

DECLARE @DropTable as VARCHAR(MAX)
SET @DropTable = 'DROP TABLE IF EXISTS [dbo].[' + @Company + '$LSC Posted CO Header$5ecfc871-5d82-43f1-9c54-59685e82318d]'
EXEC (@DropTable)

SET @CreateTable = 'CREATE TABLE [dbo].[' + @Company + '$LSC Posted CO Header$5ecfc871-5d82-43f1-9c54-59685e82318d](
[timestamp] [timestamp] NOT NULL,
	[Document ID] [nvarchar](20) NOT NULL,
	[Store No_] [nvarchar](10) NOT NULL,
	[Collect Shelf] [nvarchar](20) NOT NULL,
	[Created at Store] [nvarchar](10) NOT NULL,
	[Created] [datetime] NOT NULL,
	[Status] [int] NOT NULL,
	[Terminal No_] [nvarchar](10) NOT NULL,
	[Customer No_] [nvarchar](20) NOT NULL,
	[Member Card No_] [nvarchar](100) NOT NULL,
	[Member Contact No_] [nvarchar](20) NOT NULL,
	[Collect Time Limit] [datetime] NOT NULL,
	[Source Type] [int] NOT NULL,
	[Inventory Transfer] [tinyint] NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
	[Address] [nvarchar](100) NOT NULL,
	[Address 2] [nvarchar](50) NOT NULL,
	[City] [nvarchar](51) NOT NULL,
	[County] [nvarchar](30) NOT NULL,
	[Post Code] [nvarchar](51) NOT NULL,
	[Country_Region Code] [nvarchar](10) NOT NULL,
	[Phone No_] [nvarchar](30) NOT NULL,
	[Email] [nvarchar](80) NOT NULL,
	[House_Apartment No_] [nvarchar](30) NOT NULL,
	[Mobile Phone No_] [nvarchar](30) NOT NULL,
	[Daytime Phone No_] [nvarchar](30) NOT NULL,
	[Territory Code] [nvarchar](10) NOT NULL,
	[Ship Order POS Flag] [nvarchar](250) NOT NULL,
	[Ship Order] [tinyint] NOT NULL,
	[Ship-to Name] [nvarchar](100) NOT NULL,
	[Ship-to Address] [nvarchar](100) NOT NULL,
	[Ship-to Address 2] [nvarchar](50) NOT NULL,
	[Ship-to City] [nvarchar](30) NOT NULL,
	[Ship-to County] [nvarchar](30) NOT NULL,
	[Ship-to Post Code] [nvarchar](20) NOT NULL,
	[Ship-to Country_Region Code] [nvarchar](10) NOT NULL,
	[Ship-to Phone No_] [nvarchar](30) NOT NULL,
	[Ship-to Email] [nvarchar](80) NOT NULL,
	[Ship-to House_Apartment No_] [nvarchar](30) NOT NULL,
	[Click and Collect Order] [tinyint] NOT NULL,
	[Shipping Agent Code] [nvarchar](10) NOT NULL,
	[Shipping Agent Service Code] [nvarchar](10) NOT NULL,
	[Sourcing Location] [nvarchar](10) NOT NULL,
	[Sourcing Order Type] [int] NOT NULL,
	[Status Info] [int] NOT NULL,
	[Show as posted (Int)] [tinyint] NOT NULL,
	[Vendor Sourcing] [tinyint] NOT NULL,
	[Deposit Payment] [tinyint] NOT NULL,
	[External ID] [nvarchar](50) NOT NULL,
	[CancelledOrder] [tinyint] NOT NULL,
	[Original Document ID] [nvarchar](20) NOT NULL,
	[Refund Status] [int] NOT NULL,
	[Requested Delivery Date] [datetime] NOT NULL,
	[$systemId] [uniqueidentifier] NOT NULL,
	[$systemCreatedAt] [datetime] NOT NULL,
	[$systemCreatedBy] [uniqueidentifier] NOT NULL,
	[$systemModifiedAt] [datetime] NOT NULL,
	[$systemModifiedBy] [uniqueidentifier] NOT NULL,
	[Scan Pay Go] [tinyint] NOT NULL,
 CONSTRAINT [' + @Company + '$LSC Posted CO Header$5ecfc871-5d82-43f1-9c54-59685e82318d$Key1] PRIMARY KEY CLUSTERED 
(
	[Document ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [' + @Company + '$LSC Posted CO Header$5ecfc871-5d82-43f1-9c54-59685e82318d$$systemId] UNIQUE NONCLUSTERED 
(
	[$systemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]'

EXEC(@CreateTable)

END
SET @i = @i + 1
END
